package com.ojoin.trade.common.utils.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;

import org.apache.commons.beanutils.BeanMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;

import com.ojoin.trade.common.utils.BeanMapUtils;
import com.ojoin.trade.common.utils.CommonUtils;
import com.ojoin.trade.common.utils.LogUtils;

/**
 * excel导出模板
 * list 第一列必须动态填充
 */
public class ExcelTmpl {

	private static Logger log = LoggerFactory.getLogger(ExcelTmpl.class);

	private HSSFWorkbook hbook = null;

	private OutputStream out;

	private static Pattern ptSqlatoms = Pattern
			.compile("(?<=\\$\\{)[^\\}]*(?=\\})");

	public ExcelTmpl(String tmplname, String filename, ServletOutputStream os)
			throws IOException {
		genCopyOfTpl(tmplname, os);
	}

	public ExcelTmpl(String tmplname, OutputStream out) throws IOException {
		genCopyOfTpl(tmplname, out);
	}

	public void genCopyOfTpl(String tmplname, OutputStream os)
			throws IOException {

		InputStream in = this.getClass().getResourceAsStream(
				"/excelTpls/" + tmplname + ".xls");
		POIFSFileSystem fs1 = new POIFSFileSystem(in);
		hbook = new HSSFWorkbook(fs1);
		out = os;
		in.close();
	}

	public void wrapData(Object vo) {
		Map<String, Object> otherProp = new HashMap<String, Object>();
		BeanMap beanMap = new BeanMap(vo);
		Iterator<String> it = beanMap.keyIterator();
		while (it.hasNext()) {
			String name = it.next();
			Object value = beanMap.get(name);
			// 转换时会将类名也转换成属性，此处去掉
			if (value != null && !name.equals("class")) {
				if (value instanceof List) {
					wrapData(name, (List) value);
				} else if (!BeanUtils.isSimpleValueType(value.getClass())) {
					Map objM = BeanMapUtils.toMap(name + ".", value);
					wrapData(objM);
				} else {
					otherProp.put(name, value);
				}
			}
		}
		wrapData(otherProp);
	}

	public void wrapData(Map obj) {
		if (hbook != null) {
			HSSFSheet sheet = hbook.getSheetAt(0);
			int rownum = sheet.getLastRowNum();
			int colnum = 0;
			for (int i = 0; i <= rownum; i++) {
				HSSFRow row = sheet.getRow(i);
				if (row == null)
					continue;
				colnum = row.getLastCellNum();
				for (int j = 0; j < colnum; j++) {
					HSSFCell cell = row.getCell((short) j);
					if (cell == null)
						continue;
					String contents = cell.getRichStringCellValue().getString();
					if (Pattern.matches("\\$\\{[\\s]*[^}]*[\\s]*\\}", contents)) {
						Matcher sqlatoms = ptSqlatoms.matcher(contents);
						sqlatoms.find();
						String param = sqlatoms.group();
						if (obj.containsKey(param)) {
							cell.setCellValue(new HSSFRichTextString(String
									.valueOf(obj.get(param))));
						}
					}
				}
			}
			return;
		}

	}

	public void wrapData(List arr) {
		wrapData(null, arr);
	}

	public void wrapData(String prefix, List arr) {

		if (null != hbook) {
			FormulaEvaluator evaluator = hbook.getCreationHelper()
					.createFormulaEvaluator();
			if (hbook.getNumberOfSheets() > 0) {
				HSSFSheet sheet = hbook.getSheetAt(0);
				int rownum = sheet.getLastRowNum();
				//LogUtils.info(log, "sheet的row行数为-->" + rownum);
				for (int rowIndex = 0; rowIndex <= rownum; rowIndex++) {
					HSSFRow row = sheet.getRow(rowIndex);
					if (null == row) {
						continue;
					}

					int colnum = row.getLastCellNum();
					//LogUtils.info(log, "第" + rowIndex + "row列数为-->" + colnum);
					for (int colIndex = 0; colIndex < colnum; colIndex++) {
						HSSFCell cell = row.getCell(colIndex);
						if (null == cell) {
							continue;
						}
						String contents = null;
						String param = null;
						boolean IsCellType = false;
						HSSFCellStyle type = cell.getCellStyle();
						String cellformula = null;
						int CellType = cell.getCellType();
						if (CellType == HSSFCell.CELL_TYPE_FORMULA) {
							IsCellType = true;
							cellformula = cell.getCellFormula();
							/*LogUtils.info(log, "第" + colIndex + "列为公式-->"
									+ cell.getCellFormula());
							LogUtils.info(log, "第" + colIndex + "列是需替换列");*/

						} else {
							contents = cell.getRichStringCellValue()
									.getString();

							/*LogUtils.info(log, "第" + colIndex + "列的值为-->"
									+ contents);*/

							
							if (!CommonUtils.isEmpty(contents)
									&& Pattern.matches(
											"\\$\\{[\\s]*[^}]*[\\s]*\\}.*",
											contents)) {

//								LogUtils.info(log, "第" + colIndex + "列是需替换列");
								Matcher sqlatoms = ptSqlatoms.matcher(contents);
								sqlatoms.find();
								param = sqlatoms.group();

								if (!CommonUtils.isEmpty(prefix)
										&& !CommonUtils.isEmpty(param)
										&& !param.startsWith(prefix)) {
									/*LogUtils.info(log, "第" + colIndex
											+ "列是需替换列但是前缀不一样-->param:" + param
											+ "--prefix-->" + prefix);*/
									continue;
								}

							} else {
								continue;
							}

						}

						for (int recordIndex = 0; recordIndex < arr.size(); recordIndex++) {

							String realPrefix = null;
							if (!CommonUtils.isEmpty(prefix)) {
								realPrefix = prefix + ".";
							}

							Map obj = BeanMapUtils.toMap(realPrefix,
									arr.get(recordIndex));

							String replace = null;
							if (IsCellType) {
								// String formula = cell.getCellFormula();

								CellValue cellValue = evaluator.evaluate(cell);
								replace = cellValue.getNumberValue() + "";

							} else {
								replace = null == obj.get(param) ? "" : obj
										.get(param).toString();
								;
							}

							//LogUtils.info(log, "替换数据为-->" + replace);

							try {
								HSSFRow row2 = sheet.getRow(rowIndex
										+ recordIndex);
								
								
								
								if (colIndex == 0
										&& recordIndex != arr.size() - 1) {// 仅第一列遍历完,同时移动行
									sheet.shiftRows(rowIndex + recordIndex + 1,
											sheet.getLastRowNum(), 1);
								}

								if (row2 == null) {
									row2 = sheet.createRow(rowIndex
											+ recordIndex);
								}

								HSSFCell cell2 = row2.getCell(colIndex);
								if (cell2 == null) {
									cell2 = row2.createCell(colIndex);
								}

								cell2.setCellStyle(type);

								if (!CommonUtils.isEmpty(cellformula)) {
									cell2.setCellFormula(cellformula);
								}
								if (!CommonUtils.isEmpty(replace)) {
									cell2.setCellValue(replace);
								}
							} catch (Exception e) {
								LogUtils.error(log, "模板最下面必须有一行空行");
								LogUtils.error(log, e);
							}

						}

					}

				}

			}
		}

	}

	private void removeAllParams() {
		if (hbook != null) {
			HSSFSheet sheet = hbook.getSheetAt(0);
			sheet.getLastRowNum();
			int rownum = sheet.getLastRowNum();
			int colnum = 0;
			for (int i = 0; i <= rownum; i++) {
				HSSFRow row = sheet.getRow(i);
				if (row == null)
					continue;
				colnum = row.getLastCellNum();
				for (int j = 0; j < colnum; j++) {
					HSSFCell cell = row.getCell(j);
					if (cell != null) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
							continue;
						}

						String contents = cell.getRichStringCellValue()
								.getString();
						if (Pattern.matches("\\$\\{[\\s]*[^}]*[\\s]*\\}",
								contents)) {
							cell.setCellValue(new HSSFRichTextString(""));
						}
					}
				}
			}
			return;
		}
	}

	public void mergeCells(int i, int j, int k, int l) {
		if (hbook != null) {
			if (hbook.getNumberOfSheets() > 0) {
				HSSFSheet sheet = hbook.getSheetAt(0);
				sheet.addMergedRegion(new Region(i, (short) j, k, (short) l));
			}
			return;
		}
	}

	public void closeWbook() {
		if (hbook != null) {
			try {
				removeAllParams();
				hbook.write(out);
			} catch (IOException e1) {
				e1.printStackTrace();
			}
			return;
		}
	}
}
